import 'dart:io';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import '../models/favorite.dart';
import '../models/browse_history.dart';

/// 数据库服务 - 管理SQLite数据库操作
/// Database Service - Manage SQLite database operations
class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  factory DatabaseService() => _instance;
  DatabaseService._internal();

  static Database? _database;
  static const String _databaseName = 'game_wiki.db';
  static const int _databaseVersion = 1;

  /// 获取数据库实例 Get database instance
  Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  /// 初始化数据库 Initialize database (public method)
  Future<void> initDatabase() async {
    await database; // This will trigger initialization if not already done
  }

  /// 初始化数据库 Initialize database
  Future<Database> _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  /// 创建数据库表 Create database tables
  Future<void> _onCreate(Database db, int version) async {
    // 创建收藏表 Create favorites table
    await db.execute('''
      CREATE TABLE favorites (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        wiki_id TEXT NOT NULL,
        wiki_name TEXT NOT NULL,
        wiki_url TEXT NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        icon_url TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER,
        UNIQUE(wiki_id, wiki_url)
      )
    ''');

    // 创建浏览历史表 Create browse history table
    await db.execute('''
      CREATE TABLE browse_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        wiki_id TEXT NOT NULL,
        wiki_name TEXT NOT NULL,
        wiki_url TEXT NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        icon_url TEXT,
        visit_count INTEGER DEFAULT 1,
        created_at INTEGER NOT NULL,
        last_visit_at INTEGER NOT NULL,
        UNIQUE(wiki_id, wiki_url)
      )
    ''');

    // 创建索引 Create indexes
    await db.execute('CREATE INDEX idx_favorites_wiki_id ON favorites(wiki_id)');
    await db.execute('CREATE INDEX idx_favorites_created_at ON favorites(created_at)');
    await db.execute('CREATE INDEX idx_history_wiki_id ON browse_history(wiki_id)');
    await db.execute('CREATE INDEX idx_history_last_visit ON browse_history(last_visit_at)');
  }

  /// 数据库升级 Database upgrade
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // 处理数据库版本升级 Handle database version upgrades
    if (oldVersion < newVersion) {
      // 在这里添加升级逻辑 Add upgrade logic here
    }
  }

  // ==================== 收藏管理 Favorites Management ====================

  /// 添加收藏 Add favorite
  Future<int> addFavorite(Favorite favorite) async {
    final db = await database;
    try {
      return await db.insert(
        'favorites',
        favorite.toMap(),
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    } catch (e) {
      print('Error adding favorite: $e');
      rethrow;
    }
  }

  /// 删除收藏 Remove favorite
  Future<int> removeFavorite(String wikiId, String wikiUrl) async {
    final db = await database;
    return await db.delete(
      'favorites',
      where: 'wiki_id = ? AND wiki_url = ?',
      whereArgs: [wikiId, wikiUrl],
    );
  }

  /// 获取所有收藏 Get all favorites
  Future<List<Favorite>> getAllFavorites() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'favorites',
      orderBy: 'created_at DESC',
    );
    return List.generate(maps.length, (i) => Favorite.fromMap(maps[i]));
  }

  /// 检查是否已收藏 Check if favorited
  Future<bool> isFavorited(String wikiId, String wikiUrl) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'favorites',
      where: 'wiki_id = ? AND wiki_url = ?',
      whereArgs: [wikiId, wikiUrl],
      limit: 1,
    );
    return maps.isNotEmpty;
  }

  /// 根据Wiki ID获取收藏 Get favorites by wiki ID
  Future<List<Favorite>> getFavoritesByWikiId(String wikiId) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'favorites',
      where: 'wiki_id = ?',
      whereArgs: [wikiId],
      orderBy: 'created_at DESC',
    );
    return List.generate(maps.length, (i) => Favorite.fromMap(maps[i]));
  }

  // ==================== 浏览历史管理 Browse History Management ====================

  /// 添加或更新浏览历史 Add or update browse history
  Future<int> addOrUpdateHistory(BrowseHistory history) async {
    final db = await database;
    
    // 检查是否已存在 Check if exists
    final existing = await db.query(
      'browse_history',
      where: 'wiki_id = ? AND wiki_url = ?',
      whereArgs: [history.wikiId, history.wikiUrl],
      limit: 1,
    );

    if (existing.isNotEmpty) {
      // 更新现有记录 Update existing record
      final existingHistory = BrowseHistory.fromMap(existing.first);
      final updatedHistory = existingHistory.incrementVisit();
      
      return await db.update(
        'browse_history',
        updatedHistory.toMap(),
        where: 'id = ?',
        whereArgs: [existingHistory.id],
      );
    } else {
      // 插入新记录 Insert new record
      return await db.insert(
        'browse_history',
        history.toMap(),
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }
  }

  /// 获取浏览历史 Get browse history
  Future<List<BrowseHistory>> getBrowseHistory({int limit = 50}) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'browse_history',
      orderBy: 'last_visit_at DESC',
      limit: limit,
    );
    return List.generate(maps.length, (i) => BrowseHistory.fromMap(maps[i]));
  }

  /// 清除浏览历史 Clear browse history
  Future<int> clearBrowseHistory() async {
    final db = await database;
    return await db.delete('browse_history');
  }

  /// 删除指定浏览历史 Delete specific browse history
  Future<int> deleteBrowseHistory(int id) async {
    final db = await database;
    return await db.delete(
      'browse_history',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 根据Wiki ID获取浏览历史 Get browse history by wiki ID
  Future<List<BrowseHistory>> getHistoryByWikiId(String wikiId) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'browse_history',
      where: 'wiki_id = ?',
      whereArgs: [wikiId],
      orderBy: 'last_visit_at DESC',
    );
    return List.generate(maps.length, (i) => BrowseHistory.fromMap(maps[i]));
  }

  /// 获取热门浏览记录 Get popular browse records
  Future<List<BrowseHistory>> getPopularHistory({int limit = 10}) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'browse_history',
      orderBy: 'visit_count DESC, last_visit_at DESC',
      limit: limit,
    );
    return List.generate(maps.length, (i) => BrowseHistory.fromMap(maps[i]));
  }

  /// 关闭数据库 Close database
  Future<void> close() async {
    final db = _database;
    if (db != null) {
      await db.close();
      _database = null;
    }
  }
}